package org.kadicon.android.database;

import java.util.ArrayList;

import android.app.ListActivity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.widget.ArrayAdapter;
import android.widget.EditText;

public class MyDatabase extends ListActivity {
	/** Called when the activity is first created. */
	private SQLiteOpenHelper mOpenHelper;
	private static final String MY_DATABASE_NAME = "sample.db";
	private static final String MY_DATABASE_TABLE = "sample_table";
	private static final int MY_DATABASE_VERSION = 5;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, MY_DATABASE_NAME, null, MY_DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE " + MY_DATABASE_TABLE + "("
					+ "_id INTEGER PRIMARY KEY," + "name VARCHAR, "
					+ "nickname VARCHAR, " + "role VARCHAR, " + "age INT(3));");

			// insert default alarms
			String insertMe = "INSERT INTO " + MY_DATABASE_TABLE
					+ "(name, nickname, role, age) " + "VALUES ";
			db.execSQL(insertMe + "('Jblee','Jblee@','s/w', 40);");
			db.execSQL(insertMe + "('SJS','SJS@','s/w', 36);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion,
				int currentVersion) {
			Log.v("DB_LOG", "Upgrading " + MY_DATABASE_TABLE
					+ " database from version " + oldVersion + " to "
					+ currentVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + MY_DATABASE_TABLE);
			onCreate(db);
		}
	}

	@Override
	public void onCreate(Bundle icicle) {
		super.onCreate(icicle);

		mOpenHelper = new DatabaseHelper(this);

		SQLiteDatabase db = mOpenHelper.getReadableDatabase();

		EditText et = new EditText(this);
		et.setSelection(et.getText().length());
		ArrayList<String> results = new ArrayList<String>();

		Cursor c = db.query(MY_DATABASE_TABLE, new String[] { "name",
				"nickname", "role", "age" }, null, null, null, null, null);

		try {
			int NameColumn = c.getColumnIndex("name");
			int NickNameColumn = c.getColumnIndex("nickname");
			int RoleColumn = c.getColumnIndex("role");
			int ageColumn = c.getColumnIndex("age");
			int i = 0;

			if (c.moveToFirst()) {
				do {
					i++;
					String Name = c.getString(NameColumn);
					String NickName = c.getString(NickNameColumn);
					String Role = c.getString(RoleColumn);
					int age = c.getInt(ageColumn);
					results.add("" + i + ":" + Name + ":" + NickName + "("
							+ Role + ":" + age + ")");
				} while (c.moveToNext());
			}
		} finally {
			if (c != null)
				c.close();
		}

		this.setListAdapter(new ArrayAdapter<String>(this,
				android.R.layout.simple_list_item_1, results));
	}
}